package batch.imei;

import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.log4j.*;

import java.util.ArrayList;
import java.sql.*;
import java.sql.Connection;

public class fixImeiForTur
{
    //private final String ORAURL = "jdbc:oracle:thin:@eu-stgdb:1521:ORAEU";
    private final String ORAURL = "jdbc:oracle:thin:@eu-anydb:1521:ORAEU";

    public static void main(String[] args)
    {
        Logger.getLogger("imei.fixImeiForTur").info("( * ) Start.");

        fixImeiForTur obj = new fixImeiForTur();
        obj.init();

        Logger.getLogger("imei.fixImeiForTur").info("( * ) End.");
    }


    private void init(){
        Connection conn = null;
        Statement    stmt = null;
        ResultSet    rs   = null;

        int iRE = 0;
        int iCnt = 0;

        ArrayList params = new ArrayList();

        StringBuffer sQuery = new StringBuffer()
                             .append("SELECT ID, IMEI FROM IMEI \n");

        StringBuffer uQuery = new StringBuffer()
                             .append("UPDATE IMEI                          \n")
                             .append("   SET IMEI = REPLACE(IMEI, ' ', '') \n")
                             .append(" WHERE ID = ?                        \n");

        StringBuffer dQuery = new StringBuffer()
                             .append("DELETE FROM IMEI  \n")
                             .append(" WHERE IMEI =  ?  \n")
                             .append("   AND ID   <> ?  \n");

        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(ORAURL, "turkey_db", "Turk2y09");
            conn.setAutoCommit(false);

            QueryRunner qRunner = new QueryRunner();


/*
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sQuery.toString());

            while(rs.next()){
                params.clear();
                params.add(rs.getObject(1));

                iRE = qRunner.update(conn, uQuery.toString(), params.toArray());

                Logger.getLogger("imei.fixImeiForTur").info("REPLACE SPACE TO NULL ==== " + ObjectUtils.toString(rs.getObject(1)) + " [" + iRE + "]");
            }

            rs = null;
            stmt = null;
*/
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sQuery.toString());

            while(rs.next()){
                params.clear();
                params.add(rs.getObject(2));
                params.add(rs.getObject(1));

                iRE = qRunner.update(conn, dQuery.toString(), params.toArray());
                conn.commit();

                Logger.getLogger("imei.fixImeiForTur").info("DELETE DUPLICATE IMEI ==== " + ObjectUtils.toString(rs.getObject(1)) + " === " + ObjectUtils.toString(rs.getObject(2)) + " [" + iRE + "]");
            }
        }
        catch(Exception ex) {
            if(conn != null) try { conn.rollback(); } catch(Exception ee) {}
            ex.printStackTrace();
            Logger.getLogger("imei.fixImeiForTur").info(ex.toString());
        }
        finally {
            if (rs   != null) try { rs.close();   } catch (Exception e) { e.printStackTrace(); }
            if (stmt != null) try { stmt.close(); } catch (Exception e) { e.printStackTrace(); }
            if (conn != null) try { conn.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }
}
